
/**
 *
 */
package com.serviceprovider.business.module.admin.role.services.dao.impl;

//~--- non-JDK imports --------------------------------------------------------

import com.serviceprovider.business.module.admin.role.model.Role;
import com.serviceprovider.business.module.admin.role.services.dao.interfaces.IRoleService;
import com.serviceprovider.business.module.common.Page;
import com.serviceprovider.business.module.common.PaginationHelper;
import com.serviceprovider.business.module.user.model.impl.User;
import com.serviceprovider.business.utility.AppUtil;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Service;

//~--- JDK imports ------------------------------------------------------------

import java.io.*;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.StringTokenizer;

/**
 * @author mrduongnv@gmail.com
 *
 */
@Service("roleService")
public class RoleServiceDaoImpl extends JdbcDaoSupport implements IRoleService, Serializable {

    /** Logger for this class and subclasses */
    protected final Log logger = LogFactory.getLog(getClass());

    @Autowired
    public RoleServiceDaoImpl(@Qualifier("jdbcTemplate") JdbcTemplate jdbcTemplate) {
        setJdbcTemplate(jdbcTemplate);
    }

    public List<Role> findAll() {
        String     sql   = "select id,name,description,active,comments from role";    // $NON-NLS-1$
        List       list  = getJdbcTemplate().queryForList(sql);
        List<Role> roles = new ArrayList<Role>();
        Map        row;
        Role       aux;

        for (Object obj : list) {
            row = (Map) obj;
            aux = new Role();
            aux.setId(new Integer(row.get("id").toString()));    // $NON-NLS-1$
            aux.setName((String) row.get("name"));               // $NON-NLS-1$
            aux.setDescription((String) row.get("description"));
            aux.setActive(AppUtil.getBoolean(row.get("active")));
            aux.setComments((String) row.get("comments"));
            roles.add(aux);
        }

        return roles;
    }

    /*
     * (non-Javadoc)
     *
     * @see springjdbc.example.service.RoleService#getRole(int)
     */
    public Role getRoleById(int id) {
        RowMapper mapper = new RowMapper() {
            public Role mapRow(ResultSet rs, int rowNum) throws SQLException {
                Role role = new Role();

                RoleServiceDaoImpl.mapRow(role, rs);

                return role;
            }
        };
        final String sql = "select id,name,description,active,comments from role where id = ?";    // $NON-NLS-1$

        try {
            return (Role) getJdbcTemplate().queryForObject(sql, new Object[] { id }, mapper);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    private static void mapRow(Role role, ResultSet rs) throws SQLException {
        role.setId(rs.getInt("id"));                         // $NON-NLS-1$
        role.setName(rs.getString("name"));                  // $NON-NLS-1$
        role.setDescription(rs.getString("description"));    // $NON-NLS-1$
        role.setActive(rs.getBoolean("active"));             // $NON-NLS-1$
        role.setComments(rs.getString("comments"));          // $NON-NLS-1$
    }

    /*
     * (non-Javadoc)
     *
     * @see springjdbc.example.service.RoleService#getRole(java.lang.String)
     */
    public Role getRoleByName(String name) {
        RowMapper mapper = new RowMapper() {
            public Role mapRow(ResultSet rs, int rowNum) throws SQLException {
                Role role = new Role();

                RoleServiceDaoImpl.mapRow(role, rs);

                return role;
            }
        };
        final String sql = "select id,name,description,active,comments from role where name = ? limit 1";    // $NON-NLS-1$

        try {
            return (Role) getJdbcTemplate().queryForObject(sql, new Object[] { name }, mapper);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    /*
     * (non-Javadoc)
     *
     * @see springjdbc.example.service.RoleService#getRoleId(java.lang.String)
     */
    public Integer getRoleId(String name) {
        try {
            return getJdbcTemplate().queryForInt("select id from role where name = ?", new Object[] { name });    // $NON-NLS-1$
        } catch (EmptyResultDataAccessException e) {
            return 0;
        }
    }

    /*
     * (non-Javadoc)
     *
     * @see
     * springjdbc.example.service.RoleService#insertRole(springjdbc.example.
     * domain.Role)
     */
    public void insertRole(Role role) {
        final String sql = "insert into role (name,description,active,comments) values(?,?,?,?)";    // $NON-NLS-1$

        getJdbcTemplate().update(sql, new Object[] { role.getName(), role.getDescription(), role.isActive(),
                role.getComments() });
    }

    public void updateRole(Role role) {
        final String sql = "update role set name = ? , description = ?, active = ?, comments = ? where id = ?";    // $NON-NLS-1$

        getJdbcTemplate().update(sql, new Object[] { role.getName(), role.getDescription(), role.isActive(),
                role.getComments(), role.getId() });
    }

    public void deleteRolesById(int[] idList) {
        final String sql = "delete from role where id = ?";    // $NON-NLS-1$

        for (int anIdList : idList) {
            getJdbcTemplate().update(sql, new Object[] { anIdList });
        }
    }

    public void resetDb(List<InputStream> ins) throws IOException, SQLException {
        StringBuffer s = new StringBuffer();

        for (InputStream in : ins) {
            BufferedReader r = new BufferedReader(new InputStreamReader(in));

            while (true) {
                String tmp = r.readLine();

                if (tmp != null) {
                    s.append(tmp).append("\n");
                } else {
                    break;
                }
            }

            in.close();
        }

        Connection conn = getJdbcTemplate().getDataSource().getConnection();

        conn.setAutoCommit(false);

        StringTokenizer tk   = new StringTokenizer(s.toString(), ";");
        String          done = "";

        while (tk.hasMoreTokens()) {
            String sql = tk.nextToken();

            if (sql.trim().length() == 0) {
                continue;
            }

            if (sql.startsWith("--")) {
                continue;
            }

            if (sql.toLowerCase().startsWith("create database") || sql.toLowerCase().startsWith("use ")) {
                continue;
            }

            try {
                conn.createStatement().execute(sql);
                done += "\n--[" + sql + "]--";
            } catch (SQLException ex) {
                throw new RuntimeException(ex.toString() + ";sql = " + sql + "\ndone=" + done);
            }
        }

        conn.commit();
    }

    @Override
    public void updateStatus(int id, boolean active) {
        String sql = "update role set active = ? where id = ?";

        getJdbcTemplate().update(sql, new Object[] { active, id });
    }

    @Override
    public Page<Role> findAll(int pageNo, int recordSize, String sortColumn, String sortOrder) {
        PaginationHelper<Role>       pageHelper = new PaginationHelper<Role>();
        ParameterizedRowMapper<Role> mapper     = new RoleRowMapper();
        String                       sql        = "select id,name,description,active,comments from role order by " + sortColumn + " " + sortOrder;    // $NON-NLS-1$
        String countSql = "SELECT COUNT(*) FROM role";

        return pageHelper.fetchPage(getJdbcTemplate(), countSql, sql, new Object[] {}, pageNo, recordSize, sortColumn,
                                    sortOrder, mapper);
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<Role> getRolesById(List<Integer> roleIdList) {
        RowMapper mapper = new RowMapper() {
            public Role mapRow(ResultSet rs, int rowNum) throws SQLException {
                Role role = new Role();

                RoleServiceDaoImpl.mapRow(role, rs);

                return role;
            }
        };
        String sqlQuery = " SELECT * FROM role WHERE id IN (" + AppUtil.getSqlIN(roleIdList) + ")";

        return getJdbcTemplate().query(sqlQuery, mapper);
    }

    public void retrieveRole(List<User> userList) {

        // making a list of role id
        List<Integer> roleIdList = new ArrayList<Integer>();

        for (User user : userList) {
            if (user.getRole() != null) {
                roleIdList.add(user.getRole().getId());
            }
        }

        // finding all roles from the list roleIdList
        List<Role> roleList = this.getRolesById(roleIdList);

        // Creating a map from roleList - key:role id value:role
        Map<Integer, Role> roleMap = new HashMap<Integer, Role>();

        for (Role role : roleList) {
            roleMap.put(role.getId(), role);
        }

        // Linking Users with its role
        for (User user : userList) {
            if (user.getRole() != null) {
                user.setRole(roleMap.get(user.getRole().getId()));
            }
        }
    }

    @Override
    public void retrieveRole(User user) {
        if (user.getRole() == null) {
            return;
        }

        user.setRole(this.getRoleById(user.getRole().getId()));
    }

    private static class RoleRowMapper implements ParameterizedRowMapper<Role> {
        @Override
        public Role mapRow(ResultSet rs, int index) throws SQLException {
            Role role = new Role();

            role.setId(rs.getInt("id"));                         // $NON-NLS-1$
            role.setName(rs.getString("name"));                  // $NON-NLS-1$
            role.setDescription(rs.getString("description"));    // $NON-NLS-1$
            role.setActive(rs.getBoolean("active"));             // $NON-NLS-1$
            role.setComments(rs.getString("comments"));          // $NON-NLS-1$

            return role;
        }
    }
}



